package cn.edu.cug.cs.gtl.docdb.core;

import cn.edu.cug.cs.gtl.common.Identifier;
import cn.edu.cug.cs.gtl.io.Storable;

import java.io.DataInput;
import java.io.DataOutput;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 文档的分类目录，数据表如下：采用PostgreSQL
 * identifier              varchar(255)
 * name                    varchar(255)
 * comment                 varchar(1024)
 * parent_identifier       varchar(255)
 */
public class DocCatalog implements Storable {
    //目录的ID
    protected Identifier identifier;
    //目录名称
    protected String name;
    //目录注释与说明
    protected String comment;
    //父目录的ID
    protected Identifier parentIdentifier;

    final private  static  String CATALOG_TABLE_NAME="docdb_catalog_table";
    final private  static  String SQL_DROP_CATALOG_TABLE= "DROP TABLE IF EXISTS "+CATALOG_TABLE_NAME;
    final private  static  String SQL_CREATE_CATALOG_TABLE= "CREATE TABLE IF NOT EXISTS " + CATALOG_TABLE_NAME + " ( identifier  VARCHAR(255) PRIMARY KEY, name VARCHAR(255), comment VARCHAR(1024), parent_identifier VARCHAR(255) )";
    final private  static  String SQL_DELETE_CATALOG_TABLE_DATA= "DELETE FROM " + CATALOG_TABLE_NAME + " WHERE identifier = ?";
    final private  static  String SQL_SELECT_CATALOG_TABLE_DATA= "SELECT name,comment,parent_identifier FROM " + CATALOG_TABLE_NAME + " WHERE identifier = ?";
    final private  static  String SQL_SELECT_CATALOG_TABLE_DATA_BY_NAME= "SELECT identifier,comment,parent_identifier FROM " + CATALOG_TABLE_NAME + " WHERE name = ?";
    final private  static  String SQL_SELECT_CATALOG_TABLE_DATA_BY_PARENT= "SELECT identifier,name,comment FROM " + CATALOG_TABLE_NAME + " WHERE parent_identifier = ?";
    final private  static  String SQL_DELETE_ALL_CATALOG_TABLE_DATA= "DELETE FROM " + CATALOG_TABLE_NAME ;
    final private  static  String SQL_INSERT_CATALOG_TABLE_DATA= "INSERT INTO " + CATALOG_TABLE_NAME + " VALUES (?,?,?,?)";
    final private  static  String SQL_UPDATE_CATALOG_TABLE_DATA= "UPDATE " + CATALOG_TABLE_NAME + " SET name = ?, comment=?, parent_identifier=? WHERE identifier=?";

    public Identifier getIdentifier() {
        return identifier;
    }

    public void setIdentifier(Identifier identifier) {
        this.identifier = identifier;
    }

    public Identifier getParentIdentifier() {
        return parentIdentifier;
    }

    public void setParentIdentifier(Identifier identifier) {
        this.parentIdentifier = identifier;
    }


    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getComment() {
        return comment;
    }

    public void setComment(String comment) {
        this.comment = comment;
    }


    public DocCatalog(){
        identifier = Identifier.create();
        name = "noname";
        comment = "no comment";
        parentIdentifier = Identifier.create(0L);
    }
    @Override
    public Object clone() {
        DocCatalog docCatalog = new DocCatalog();
        docCatalog.identifier.copyFrom(this.identifier);
        docCatalog.name = this.name;
        docCatalog.comment=this.comment;
        docCatalog.parentIdentifier.copyFrom(this.parentIdentifier);
        return docCatalog;
    }

    @Override
    public boolean load(DataInput dataInput) throws IOException {
        identifier.load(dataInput);
        name=dataInput.readUTF();
        comment = dataInput.readUTF();
        parentIdentifier.load(dataInput);
        return true;
    }

    @Override
    public boolean store(DataOutput dataOutput) throws IOException {
        identifier.store(dataOutput);
        dataOutput.writeUTF(name);
        dataOutput.writeUTF(comment);
        parentIdentifier.store(dataOutput);
        return true;
    }

    public static boolean createCatalogTable(Connection connection) throws Exception{
        Statement statement = connection.createStatement();
        statement.execute(SQL_CREATE_CATALOG_TABLE);
        statement.close();
        return true;
    }

    public static boolean dropCatalogTable(Connection connection) throws Exception{
        Statement statement = connection.createStatement();
        statement.execute(SQL_DROP_CATALOG_TABLE);
        statement.close();
        return true;
    }

    public static boolean insert(Connection connection, DocCatalog docCatalog) throws SQLException{
        PreparedStatement statement = connection.prepareStatement(SQL_INSERT_CATALOG_TABLE_DATA);
        statement.setString(1,docCatalog.getIdentifier().toString());
        statement.setString(2,docCatalog.getName());
        statement.setString(3,docCatalog.getComment());
        statement.setString(4,docCatalog.getParentIdentifier().toString());
        statement.execute();
        statement.close();
        return true;
    }
    public static boolean delete(Connection connection, DocCatalog docCatalog) throws SQLException{
        PreparedStatement statement = connection.prepareStatement(SQL_DELETE_CATALOG_TABLE_DATA);
        statement.setString(1,docCatalog.getIdentifier().toString());
        statement.execute();
        statement.close();
        return true;
    }
    public static boolean update(Connection connection, DocCatalog docCatalog) throws SQLException{
        PreparedStatement statement = connection.prepareStatement(SQL_UPDATE_CATALOG_TABLE_DATA);
        statement.setString(1,docCatalog.getName());
        statement.setString(2,docCatalog.getComment());
        statement.setString(3,docCatalog.getParentIdentifier().toString());
        statement.setString(4,docCatalog.getIdentifier().toString());
        statement.execute();
        statement.close();
        return true;
    }

    /**
     * 返回下一个目录ID
     * @param connection
     * @return
     * @throws SQLException
     */
    public static Identifier next(Connection connection) throws SQLException{
        String SQL_NEXT="select max(identifier) from "+CATALOG_TABLE_NAME;
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(SQL_NEXT);
        Identifier i = Identifier.create(0L);
        if(rs.next()){
            String s = rs.getString(1);
            if (s != null) {
                i = Identifier.fromString(s);
            }
            i.increase();
        }
        statement.close();
        return i;
    }

    /**
     * 获取指定目录对象
     * @param connection
     * @param identifier
     * @return
     * @throws SQLException
     */
    public static DocCatalog selectByIdentifier(Connection connection, Identifier identifier) throws SQLException{
        PreparedStatement statement = connection.prepareStatement(SQL_SELECT_CATALOG_TABLE_DATA);
        statement.setString(1,identifier.toString());
        ResultSet rs = statement.executeQuery();
        DocCatalog docCatalog = new DocCatalog();
        if(rs.next()){
            docCatalog.setName(rs.getString(1));
            docCatalog.setComment(rs.getString(2));
            docCatalog.setParentIdentifier(Identifier.fromString(rs.getString(3)));
        }
        statement.close();
        docCatalog.setIdentifier((Identifier) identifier.clone());
        return docCatalog;
    }

    /**
     * 按照名称，获取指定目录对象
     * @param connection
     * @param name
     * @return
     * @throws SQLException
     */
    public static List<DocCatalog> selectByName(Connection connection, String  name) throws SQLException{
        PreparedStatement statement = connection.prepareStatement(SQL_SELECT_CATALOG_TABLE_DATA_BY_NAME);
        statement.setString(1,name);
        ResultSet rs = statement.executeQuery();
        List<DocCatalog> ls = new ArrayList<>();
        while(rs.next()){
            DocCatalog docCatalog = new DocCatalog();
            docCatalog.setIdentifier(Identifier.fromString(rs.getString(1)));
            docCatalog.setComment(rs.getString(2));
            docCatalog.setParentIdentifier(Identifier.fromString(rs.getString(3)));
            docCatalog.setName(name);
            ls.add(docCatalog);
        }
        statement.close();
        return ls;
    }

    /**
     * 获取指定目录下的所有的子目录对象
     * @param connection
     * @param identifier
     * @return
     * @throws SQLException
     */
    public static List<DocCatalog> selectByParentIdentifier(Connection connection, Identifier identifier) throws SQLException{
        PreparedStatement statement = connection.prepareStatement(SQL_SELECT_CATALOG_TABLE_DATA_BY_PARENT);
        statement.setString(1,identifier.toString());
        ResultSet rs = statement.executeQuery();
        List<DocCatalog> ls = new ArrayList<>();
        while(rs.next()){
            DocCatalog docCatalog = new DocCatalog();
            docCatalog.setIdentifier(Identifier.fromString(rs.getString(1)));
            docCatalog.setName(rs.getString(2));
            docCatalog.setComment(rs.getString(3));
            docCatalog.setParentIdentifier((Identifier) identifier.clone());
            ls.add(docCatalog);
        }
        statement.close();
        return ls;
    }
}
